# -*- coding:utf-8 -*-

class TaskManageSql(object):
    """任务SQL"""
    """任务团队列表"""
    TASK_GROUP_LIST = "SELECT gt.*,t.content FROM group_task as gt " \
                      "INNER JOIN task as t ON t.id=gt.task_id"
    
    """任务团队编辑"""
    EDIT_TASK_GROUP = "UPDATE group_task SET task_id=%s, remarks=%s, user_id=%s,project_id=%s WHERE id=%s"
    
    """任务团队添加成员"""
    ADD_TASK_GROUP = "INSERT INTO group_task(task_id, remarks,user_id,project_id) VALUES (%s, %s, %s,%s)"
    
    """任务团队整体删除"""
    DEL_TASK_GROUP_BY_TASK = "DELETE FROM group_task WHERE task_id=%s"
    
    """任务团队信息"""
    TASK_GROUP_INFO = "SELECT u.id,gt.id as \"gt_id\",t.content,gt.remarks,u.user_name,u2.user_name as \"leader\" " \
                      "FROM group_task as gt INNER JOIN user_info as u on gt.user_id=u.id " \
                      "INNER JOIN task as t on gt.task_id=t.id " \
                      "INNER JOIN user_info as u2 on u2.id=t.leader WHERE gt.task_id=%s;"
    
    """任务列表(原始)"""
    TASK_LIST_OLD = "SELECT gt.*,u.user_name,t.*,u2.user_name as \"leader\"  " \
                    "FROM group_task as gt INNER JOIN user_info as u on gt.user_id=u.id " \
                    "INNER JOIN task as t on gt.task_id=t.id " \
                    "INNER JOIN user_info as u2 on u2.id=t.leader " \
                    "INNER JOIN project as p ON p.id=t.object_id " \
                    "INNER JOIN user_info as u3 on u3.id=p.leader WHERE u3.id=%s"
    
    """任务列表"""
    TASK_LIST = "SELECT t.*,p.name,u.user_name as \"leader\" " \
                "FROM task as t " \
                "INNER JOIN user_info as u ON u.id=t.leader " \
                "INNER JOIN project as p ON t.object_id=p.id " \
                "INNER JOIN user_info as u2 ON u2.id=p.leader WHERE u2.id=%s or %s>2;"
    
    """任务搜索"""
    TASK_SEARCH = "SELECT t.*,p.name,u.user_name as \"leader\" " \
                  "FROM task as t " \
                  "INNER JOIN user_info as u ON u.id=t.leader " \
                  "INNER JOIN project as p ON t.object_id=p.id " \
                  "INNER JOIN user_info as u2 ON u2.id=p.leader WHERE (u2.id=%s or %s>2) AND t.content LIKE '%%'||%s||'%%';"
    
    """任务列表"""
    PROJECT_TASK_LIST = "SELECT t.*,p.name,u.user_name as \"leader\" " \
                        "FROM task as t " \
                        "INNER JOIN user_info as u ON u.id=t.leader " \
                        "INNER JOIN project as p ON t.object_id=p.id " \
                        "INNER JOIN user_info as u2 ON u2.id=p.leader WHERE t.object_id=%s"
    
    """任务添加"""
    ADD_TASK = "INSERT INTO task(object_id, leader, content, start_date, end_date, plane_time, status,process)" \
               " VALUES (%s, %s, %s,%s, %s, %s,%s,0)returning id"
    
    """任务删除"""
    DEL_TASK = "DELETE FROM task WHERE id=%s"
    
    """任务信息"""
    TASK_INFO = "SELECT t.id, o.name, u.user_name as \"leader\", t.content,o.id as \"object_id\"" \
                ",t.start_date, t.end_date, t.plane_time, t.status,t.process " \
                "FROM task as t INNER JOIN project as o ON t.object_id=o.id " \
                "INNER JOIN user_info as u  ON t.leader=u.id WHERE t.id=%s"
    
    """任务编辑"""
    EDIT_TASK = "UPDATE task SET leader=%s, " \
                "start_date=%s, end_date=%s, plane_time=%s, status=%s,process=%s WHERE id=%s;"
    
    """任务编辑"""
    OVER_TASK = "UPDATE task SET leader=%s, " \
                "start_date=%s, end_date=%s, plane_time=%s, status=%s,process=100 WHERE id=%s;DELETE FROM group_task WHERE task_id=%s"
    
    TASK_GROUP_EDIT_USER = "UPDATE group_task SET task_id=%s, remarks=%s, user_id=%s WHERE id=%s"
    
    TASK_GROUP_DEL_USER = "DELETE FROM group_task WHERE user_id=%s and task_id=%s"
    
    """所有leader列表"""
    TASK_GROUP_LEADER_LIST = "SELECT u.* FROM group_project as gp " \
                             "INNER JOIN user_info as u ON gp.user_id=u.id " \
                             "WHERE u.company_post<3 and gp.object_id " \
                             "IN(SELECT gp.object_id FROM group_project as gp " \
                             "INNER JOIN project as p ON gp.object_id = p.id " \
                             "WHERE gp.user_id=%s)"
    
    """项目中leader列表"""
    PROJETC_TASK_GROUP_LEADER_LIST = "SELECT u.id,u.user_name FROM group_project as gp " \
                                     "INNER JOIN user_info as u ON gp.user_id=u.id " \
                                     "WHERE u.company_post<3 and gp.object_id=%s ;"
    
    TASK_INFO_PERSON = "SELECT p.id,p.name, gp.remarks,u.user_name as \"leader\", p.status,gp.user_id,p.create_date," \
                       "p.end_date,p.plane_time FROM group_task as gp " \
                       "INNER JOIN task as p ON gp.object_id=p.id " \
                       "INNER JOIN user_info as u ON u.id=p.leader Where gp.user_id=%s and status<3"
    
    TASK_INFO_PERSON_ALL = "SELECT p.id,p.name, gp.remarks,u.user_name as \"leader\", p.status,gp.user_id,p.create_date," \
                           "p.end_date,p.plane_time FROM group_task as gp " \
                           "INNER JOIN task as p ON gp.object_id=p.id " \
                           "INNER JOIN user_info as u ON u.id=p.leader Where status<2"
    
    TASK_EMPTY_PERSON = "SELECT u.* from group_project as gp  INNER JOIN user_info as u ON u.id=gp.user_id WHERE gp.object_id=%s EXCEPT SELECT u.* FROM group_task as gt INNER JOIN user_info as u ON gt.user_id=u.id"
    
    TASK_EDIT_LEADER = "UPDATE task SET leader=%s WHERE id=%s"
